﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using _2308NA.Model;
using System.Data.SqlClient;
using System.Xml.Linq;

namespace _2308NA.DAL
{
    public class UsersDal
    {
        SQLHelp help = new SQLHelp();

        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="phone">手机号（账户）</param>
        /// <param name="pwd">密码</param>
        /// <returns></returns>
        public Users Login(string phone, string pwd)
        {

            Users users = null;

            // 账户（手机） 和 密码 来查询数据

            //2.SQL命令和参数
            string sql = "select * from Users where phone=@phone and pwd=@pwd and status=1";
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@phone",phone),
                new SqlParameter("@pwd",pwd)
            };

            ////1.连接数据库
            //SqlConnection conn = new SqlConnection("server=.;database=2308NA;uid=sa;pwd=123;");
            //conn.Open();
            ////3.执行工具和执行过程
            //SqlCommand cmd = new SqlCommand(sql,conn);
            //cmd.Parameters.AddRange(parameters);  //参数
            //SqlDataReader sdr = cmd.ExecuteReader();

            SqlDataReader sdr = help.ExecuteReader(sql, parameters);


            //判断是否有行数
            if (sdr.HasRows)
            {
                if (sdr.Read())
                {
                    users = new Users();

                    //users.UserID = int.Parse(sdr["userID"].ToString());
                    //users.UserID = Convert.ToInt32(sdr["userID"]);
                    //users.Name = (string)sdr["Name"];

                    users.UserID = (int)sdr["userID"];
                    users.Name = sdr["name"].ToString();
                    users.Sex = sdr["sex"].ToString();
                    users.Birthday = Convert.ToDateTime(sdr["birthday"]);
                    users.Phone = sdr["phone"].ToString();
                    users.Pwd = sdr["pwd"].ToString();
                    users.Status = Convert.ToBoolean(sdr["status"]);

                }
            }

            //4.关闭连接
            sdr.Close();  //关闭读取器



            return users;
        }


        /// <summary>
        /// 列表查询
        /// </summary>
        /// <returns></returns>
        public List<Users> GetUsers(string name, int page, int size, out int count)
        {
            List<Users> list = new List<Users>();

            //分页查询的数据
            string sql = @"Select u.*,r.roleName,d.deptName from Users u 
                            inner join Roles  r on u.roleID= r.roleID 
                            inner join Department d on r.deptID=d.deptID  
                            where name like @name  
                            order by userID 
                            offset (@page-1)*@size rows   
                            fetch next @size rows only";
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@page",page),
                new SqlParameter("@size",size),
                new SqlParameter("@name","%"+name+"%")
            };
            SqlDataReader sdr = help.ExecuteReader(sql, parameters);
            if (sdr.HasRows)
            {
                while (sdr.Read())
                {
                    Users user = new Users();
                    user.UserID = (int)sdr["userID"];
                    user.Name = sdr["name"].ToString();
                    user.Sex = sdr["sex"].ToString();
                    user.Birthday = Convert.ToDateTime(sdr["birthday"]);
                    user.Phone = sdr["phone"].ToString();
                    user.Pwd = sdr["pwd"].ToString();
                    user.Status = Convert.ToBoolean(sdr["status"]);
                    user.RoleID = (int)sdr["roleID"]; ;
                    user.RoleName = sdr["roleName"].ToString();
                    user.DeptName = sdr["deptName"].ToString();

                    list.Add(user);
                }
            }
            sdr.Close();

            //求总条数
            string sqlcount = @"Select count(*) from Users   u 
                            inner join Roles  r on u.roleID= r.roleID  
                            inner join Department d on r.deptID=d.deptID   
                            where name like @name";
            SqlParameter[] parameterscount = new SqlParameter[] {
                new SqlParameter("@name","%"+name+"%")
            };
            count = (int)help.ExecuteScalar(sqlcount, parameterscount);

            return list;
        }

        public Users GetUsers(int id)
        {
            Users user = new Users();

            string sql = @"Select u.*,r.roleName,d.deptName from Users   u
                            inner join Roles  r on u.roleID= r.roleID 
                            inner join Department d on r.deptID=d.deptID 
                            where userID=@userID";
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@userID",id)
            };
            SqlDataReader sdr = help.ExecuteReader(sql, parameters);
            if (sdr.HasRows)
            {
                if (sdr.Read())
                {
                    user.UserID = (int)sdr["userID"];
                    user.Name = sdr["name"].ToString();
                    user.Sex = sdr["sex"].ToString();
                    user.Birthday = Convert.ToDateTime(sdr["birthday"]);
                    user.Phone = sdr["phone"].ToString();
                    user.Pwd = sdr["pwd"].ToString();
                    user.Status = Convert.ToBoolean(sdr["status"]);
                    user.RoleID = (int)sdr["roleID"]; ;
                    user.RoleName = sdr["roleName"].ToString();
                    user.DeptName = sdr["deptName"].ToString();
                }
            }
            sdr.Close();

            return user;
        }

        public int AddUser(Users users)
        {
            string sql = @"insert into Users values
                (@name,@roleid,@sex,@birthday,@phone,@pwd,@status)";
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@name",users.Name),
                new SqlParameter("@roleid",users.RoleID),
                new SqlParameter("@sex",users.Sex),
                new SqlParameter("@birthday",users.Birthday),
                new SqlParameter("@phone",users.Phone),
                new SqlParameter("@pwd",users.Pwd),
                new SqlParameter("@status",users.Status),
            };

            int num = help.ExecuteNonQuery(sql, parameters);
            return num;
        }

        public int UpdateUser(Users users)
        {
            string sql = @"update  Users set name=@name,sex=@sex,birthday=@birthday,
                    phone=@phone,pwd=@pwd,status=@status, roleID=@roleid 
                    where userID=@userID";
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@name",users.Name),
                new SqlParameter("@roleid",users.RoleID),
                new SqlParameter("@sex",users.Sex),
                new SqlParameter("@birthday",users.Birthday),
                new SqlParameter("@phone",users.Phone),
                new SqlParameter("@pwd",users.Pwd),
                new SqlParameter("@status",users.Status),
                new SqlParameter("@userID",users.UserID)
            };

            int num = help.ExecuteNonQuery(sql, parameters);
            return num;
        }

        public int DeleteUser(int id)
        {
            string sql = @"delete from Users 
                    where userID=@userID";
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@userID",id)
            };

            int num = help.ExecuteNonQuery(sql, parameters);
            return num;
        }

        //批量删除
        public int DeleteBatch(string ids)
        {
            string sql = @"delete from Users where userID in("+ids+")";
           
            int num = help.ExecuteNonQuery(sql, null);
            return num;
        }
    }
}
